package net.tngou.iask.dao;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import net.tngou.iask.jdbc.PageUtil;
import net.tngou.iask.jdbc.QueryHelper;
import net.tngou.iask.pojo.Health;
import net.tngou.iask.pojo.POJO;
import net.tngou.iask.pojo.Patient;
import net.tngou.iask.pojo.Question;
import net.tngou.iask.util.StringUtil;

/**
 * 自定义随访
 * @author tngou@tngou.net
 *
 */
public class AnalysisDao extends BaseDao {

	
	/**
	 * 取得患者
	 * @param analysis
	 * @param page
	 * @param size
	 * @param keyword
	 * @return
	 */
	public PageUtil	getPatient(long analysis,int page,int size,String keyword) {
		List<Map<String, Object>> list=new ArrayList<>();
		int total=0;
		String sql_r="SELECT patient.* ,analysis_patient.id as apid,analysis_patient.bdate";
		String sql_t="SELECT COUNT(*) ";
		String sql_o=" ORDER BY patient.id DESC";//排序
		String sql=" FROM `"+POJO.DABASE+"_patient` patient ,`"+POJO.DABASE+"_analysis_patient` analysis_patient" ;
		sql=sql+ " WHERE  analysis_patient.patient=patient.id  AND analysis_patient.analysis=?  ";
		if(StringUtil.isNotEmpty(keyword))
		{
			sql=sql+" AND (patient.no LIKE ? OR patient.name  LIKE ? )";			
			Object[] params={analysis,keyword+"%","%"+keyword+"%"};
			list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);			
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}else{
			Object[] params={analysis};
			list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);	
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}		
		return new PageUtil(list, page, size, total);
	}
	
	
	/**
	 * 取得患者
	 * @param analysis
	 * @return
	 */
	public List<Map<String, Object>> getPatient(long analysis) {
		String sql_r="SELECT patient.*, analysis_patient.bdate";
		String sql_o=" ORDER BY patient.id DESC";//排序
		String sql=" FROM `"+POJO.DABASE+"_patient` patient ,`"+POJO.DABASE+"_analysis_patient` analysis_patient" ;
		sql=sql+ " WHERE  analysis_patient.patient=patient.id  AND analysis_patient.analysis=?  ";	
		return QueryHelper.query(sql_r+sql+sql_o, analysis);
	}
	
	/**
	 * 取得需要统计的目录
	 * @param analysis
	 * @return
	 */
	public List<Question>	getQuestion(long analysis) {	
		String sql="SELECT question.* FROM `"+POJO.DABASE+"_question` question ,`"+POJO.DABASE+"_analysis_question` analysis_question WHERE question.id=analysis_question.question AND analysis_question.analysis=? ";
		return QueryHelper.query(Question.class, sql, analysis);
	}
	public List<Health> getHeath(Object ...ls)
	{
		String in="";
		for (int i = 0; i < ls.length; i++) {
			if(StringUtil.isNotEmpty(in))in=in+",";
			in=in+"?";
		}
		String sql="SELECT * FROM `iask_health` WHERE patient IN("+in+")";
		return QueryHelper.query(Health.class, sql, ls);
	}
	
	
	/**
	 * 添加自定义分析用户
	 * @param analysis
	 * @param patient
	 * @return
	 */
	public boolean savePatient(long analysis,long patient,Date bdate) {
		String sql="SELECT COUNT(id) FROM `iask_analysis_patient` WHERE analysis=? AND patient=?";
		long r = QueryHelper.stat(sql, analysis,patient);
		if(r==0)
		{
			sql ="INSERT INTO `iask_analysis_patient`( `analysis`, `patient`, `bdate`) VALUES (?,?,?)";
			r=QueryHelper.update(sql, analysis,patient,bdate);
		}
		return r==0?false:true;
	}
	/**
	 * 删除自定义分析用户
	 * @param analysis
	 * @param patient
	 * @return
	 */
	public boolean deletePatient(long analysis,long patient) {
		String sql="DELETE FROM `iask_analysis_patient` WHERE  analysis=? AND patient=?";
		int r = QueryHelper.update(sql, analysis,patient);
		return r==0?false:true;
	}
	
	/**
	 * 添加自定义栏目
	 * @param analysis
	 * @param question
	 * @return
	 */
	public boolean saveQuestion(long analysis,long question) {
		String sql="SELECT COUNT(id) FROM `iask_analysis_question` WHERE analysis=? AND question=?";
		long r = QueryHelper.stat(sql, analysis,question);
		if(r==0)
		{
			sql ="INSERT INTO `iask_analysis_question`( `analysis`, `question`) VALUES (?,?)";
			r=QueryHelper.update(sql, analysis,question);
		}
		return r==0?false:true;
	}
	
	/**
	 * 删除自定义栏目
	 * @param analysis
	 * @param question
	 * @return
	 */
	public boolean deleteQuestion(long analysis,long question) {
		String sql="DELETE FROM `iask_analysis_question` WHERE  analysis=? AND question=?";
		int r = QueryHelper.update(sql, analysis,question);
		return r==0?false:true;
	}
	
	/**
	 * 删除自定义消息
	 * @param analysis
	 */
	public void delete(long analysis) {
		String sql="DELETE FROM `iask_analysis_question` WHERE  analysis=?";
		QueryHelper.update(sql, analysis);
		sql="DELETE FROM `iask_analysis_patient` WHERE  analysis=? AND patient=?";
		QueryHelper.update(sql, analysis);
	}
	
	
	/**
	 * 取得没有入组的用户
	 * @param page
	 * @param size
	 * @param main
	 * @param analysis
	 * @param keyword
	 * @return
	 */
	public PageUtil getCurePage(int page,int size,long main,long analysis,String keyword) {
		List<Map<String, Object>> list=new ArrayList<>();
		int total=0;
		String sql_r="SELECT cure.*,patient.id as pid,patient.name,patient.gender,patient.no,patient.birthday,survey.name  as sname  ,disease.name as dname";
		String sql_t="SELECT COUNT(*) ";
		String sql=" FROM `"+POJO.DABASE+"_cure` cure ,`"+POJO.DABASE+"_patient` patient,`"+POJO.DABASE+"_survey` survey ,`"+POJO.DABASE+"_disease` disease WHERE"
				+ " cure.patient=patient.id AND  survey.id=cure.survey AND disease.id=cure.disease  AND cure.main=?  AND cure.patient NOT IN(SELECT patient FROM `iask_analysis_patient` WHERE analysis =?)";
				
		String sql_o=" ORDER BY cure.id DESC";//排序
		if(StringUtil.isNotEmpty(keyword))
		{
			sql =sql+" AND (cure.number LIKE ? OR patient.name LIKE ?') " ;
			Object[] params={main,analysis};
			list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);			
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}else
		{
			Object[] params={main,analysis};
			list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);			
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}

		return new PageUtil(list, page, size, total);
	}
	
	
	/**
	 * 取得没有选择的问题
	 * @param page
	 * @param size
	 * @param main
	 * @param analysis
	 * @param keyword
	 * @return
	 */
	public PageUtil getQuestionPage(int page,int size,long main,long analysis,String keyword) {
		List<Question> list=new ArrayList<>();
		int total=0;
		String sql_r="SELECT *";
		String sql_t="SELECT COUNT(id) ";
		String sql_o=" ORDER BY id DESC";//排序
		String sql=" FROM `"+POJO.DABASE+"_question`";
		if(main!=0)sql=sql+ " WHERE  main IN(0,?) AND id NOT IN(SELECT question FROM `iask_analysis_question` WHERE analysis=? )";
		else sql=sql+ " WHERE  main=? AND type=?";
		if(StringUtil.isNotEmpty(keyword))
		{
			sql=sql+" AND (name LIKE ? OR title  LIKE ? OR keyword  LIKE ?)";
			
			Object[] params={main,analysis,"%"+keyword+"%","%"+keyword+"%","%"+keyword+"%"};
			list=QueryHelper.query_slice(Question.class, sql_r+sql+sql_o, page, size, params);			
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}else{
			Object[] params={main,analysis};
			list=QueryHelper.query_slice(Question.class, sql_r+sql+sql_o, page, size, params);	
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}		
		return new PageUtil(list, page, size, total);
		
	}
	
	/**
	 * 最近N年患者总数
	 * @param analysis
	 * @param year
	 * @return
	 */
	public int generationtotal(long analysis,int year) {
		String sql="SELECT COUNT(patient) FROM `iask_analysis_patient` WHERE analysis=? AND DATE_ADD(NOW(),INTERVAL ? YEAR)>bdate";	
		return (int) QueryHelper.stat(sql, analysis,year);
		
	}
	
	/**
	 * 最近N年患者死亡
	 * @param analysis
	 * @param year
	 * @return
	 */
	public int generation(long analysis,int year) {
		String sql="SELECT COUNT(*) FROM `iask_obituary` WHERE patient IN( SELECT patient FROM `iask_analysis_patient` WHERE analysis=? AND DATE_ADD(NOW(),INTERVAL ? YEAR)<bdate)";
		return (int) QueryHelper.stat(sql, analysis,year);		
	}
	
	
	/**
	 * 取得死亡信息
	 * @return
	 */
	public List<Map<String, Object>> obituary(long analysis) {
		String sql="SELECT obituary.*,analysis_patient.bdate,patient.name ,patient.no,patient.gender ,patient.birthday FROM `iask_obituary` obituary ,`iask_analysis_patient` analysis_patient ,`iask_patient` patient WHERE obituary.patient=analysis_patient.patient AND obituary.patient=patient.id AND analysis_patient.analysis=? ";
		return QueryHelper.query(sql, analysis);
		
	}
	
	/**
	 * 死亡原因
	 * @param analysis
	 * @param type
	 * @return
	 */
	public int obituary(long analysis,int cause) {
		String sql="SELECT COUNT(*) FROM `iask_obituary` obituary,`iask_analysis_patient` analysis_patient WHERE obituary.patient=analysis_patient.patient AND analysis_patient.analysis=? AND obituary.cause=?";
		return (int) QueryHelper.stat(sql, analysis,cause);
	}
	
	
	
	
	
	
	
}
